"""
mongodb 有96条数据 name imageUrl
mysql goods 表 有2000条数据，title image_src 将mysql 的前96条数据更新到mongodb。
title 替换name image_src 替换imageUrl. 其他字段不变
{
  "_id": {
    "$oid": "662b1e77b7f3a5b948e5c135"
  },
  "productId": 62138,
  "name": "WD 西部数据 My Passport 2.5' USB3.0 2TB移动硬盘(黑色 WDBY8L0020BBK-PESN-CH)",
  "imageUrl": "https://images-cn-4.ssl-images-amazon.com/images/I/41zV8SlYd2L._SY300_QL70_.jpg",
  "categories": "存储设备|移动硬盘|电脑/办公",
  "tags": "WD|西部数据|硬盘|容量挺大的|速度快|好用"
}

pip install mysql-connector-python -i https://mirror.baidu.com/pypi/simple

db.Product.remove({})
"""

import pymongo
import mysql.connector

# 连接 MongoDB
mongo_client = pymongo.MongoClient("mongodb://localhost:27017/")
db = mongo_client["recommender"]
collection = db["Product"]

# 连接 MySQL
mysql_connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password='123456',
    database='datavisible',
)
mysql_cursor = mysql_connection.cursor()

# 从 MySQL 中获取前96条数据
mysql_cursor.execute("""
SELECT title, image_src
FROM (
    SELECT id, title, image_src, location, cate,
           ROW_NUMBER() OVER (PARTITION BY cate ORDER BY id) AS row_num
    FROM datavisible.goods
) AS subquery
WHERE row_num <= 24
ORDER BY RAND()
""")
mysql_records = mysql_cursor.fetchall()

# 获取 MongoDB 中的前96条数据
mongo_records = collection.find().limit(96)

# 更新 MongoDB 中的数据
for mongo_record, mysql_record in zip(mongo_records, mysql_records):
    # 替换字段
    mongo_record["name"] = mysql_record[0]  # 替换 name 字段
    mongo_record["imageUrl"] = mysql_record[1]  # 替换 imageUrl 字段
    # 更新数据
    collection.update_one({"_id": mongo_record["_id"]}, {"$set": {"name": mongo_record["name"], "imageUrl": mongo_record["imageUrl"]}})

# 关闭连接
mysql_cursor.close()
mysql_connection.close()
mongo_client.close()